package com.admin.util;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.admin.model.ExcelReadResult;
import com.admin.model.FileMeta;
import com.admin.model.ImportExcelReprotModel;
import com.common.model.Project;
import com.common.model.Score;

public class AdminScoreExcelTool {
	// 至少需要包含的表头
	static private ArrayList<String> excelHeadMinVal = null;

	public AdminScoreExcelTool() {
		if (excelHeadMinVal == null) {
			excelHeadMinVal = new ArrayList<>();
			excelHeadMinVal.add("题目标题");
			excelHeadMinVal.add("指导教师成绩");
			excelHeadMinVal.add("评阅人成绩");
			excelHeadMinVal.add("答辩成绩");
		}
	}

	/**
	 * 读取excel文件
	 * 
	 * @param excelFile
	 * @return 返回一个包括学生,错误信息等的bean
	 */
	public ExcelReadResult<ImportExcelReprotModel<Score>> readExcel(
			FileMeta excelFile) {
		boolean is2003 = excelFile.getFileName().endsWith(".xls");
		if (is2003) {
			return readExcelOf2003(excelFile.getInputStresm());
		} else {
			return readExcelOf2007(excelFile.getInputStresm());
		}
	}

	/**
	 * 读取2003版的excel
	 * 
	 * @param is
	 * @return
	 */
	public ExcelReadResult<ImportExcelReprotModel<Score>> readExcelOf2003(
			InputStream is) {
		HSSFWorkbook wb = null;
		HSSFSheet sheet;
		ExcelReadResult<ImportExcelReprotModel<Score>> result = new ExcelReadResult<>();
		result.setTotalDataCount(0);
		try {
			wb = new HSSFWorkbook(is);
		} catch (Exception e) {
			result.setErrorMsg("文件读取错误");
			return result;
		}
		sheet = wb.getSheetAt(0);
		if (sheet == null) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HSSFRow excelHead = sheet.getRow(0);
		if (excelHead == null) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int colCount = excelHead.getLastCellNum();
		if (colCount < excelHeadMinVal.size()) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HashMap<String, Integer> indexMap = new HashMap<>();
		int check = 0;
		for (int i = 0; i < colCount; i++) {
			HSSFCell cell = excelHead.getCell(i);
			if (cell == null) {
				continue;
			}
			String cellval = cell.getStringCellValue().toLowerCase().trim();
			if (!indexMap.containsKey(cellval)) {
				indexMap.put(cellval, i);
				if (excelHeadMinVal.contains(cellval)) {
					check++;
				}
			}
		}
		if (check < excelHeadMinVal.size()) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int rowCount = getRowsInSheet(sheet);
		result.setTotalDataCount(rowCount-1);
		if (rowCount == 1) {
			result.setErrorMsg("没有任何数据");
			return result;
		}
		for (int i = 1; i < rowCount; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			ArrayList<String> rowData = new ArrayList<>();
			for (int j = 0; j < colCount; j++) {
				HSSFCell cell = row.getCell(j);
				if (cell == null) {
					rowData.add("");
					continue;
				}
				try {
					rowData.add(cell.getStringCellValue());
				} catch (Exception e) {
					if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
						rowData.add((long) cell.getNumericCellValue() + "");
					} else {
						rowData.add("");
					}
				}
			}
			ImportExcelReprotModel<Score> report = getScoreByRow(rowData,
					indexMap);
			if (report.getErrorMsg() != null) {
				report.setErrorLine(i + 1);
			}
			result.getDataList().add(report);
		}
		return result;
	}

	public ExcelReadResult<ImportExcelReprotModel<Score>> readExcelOf2007(
			InputStream is) {
		ExcelReadResult<ImportExcelReprotModel<Score>> result = new ExcelReadResult<>();
		result.setTotalDataCount(0);
		XSSFWorkbook wb = null;
		XSSFSheet sheet = null;
		try {
			wb = new XSSFWorkbook(is);
		} catch (Exception e) {
			result.setErrorMsg("文件读取错误");
			return result;
		}
		sheet = wb.getSheetAt(0);
		if (sheet == null) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		XSSFRow excelHead = sheet.getRow(0);
		if (excelHead == null) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int colCount = excelHead.getLastCellNum();
		if (colCount < excelHeadMinVal.size()) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HashMap<String, Integer> indexMap = new HashMap<>();
		int check = 0;
		for (int i = 0; i < colCount; i++) {
			XSSFCell cell = excelHead.getCell(i);
			if (cell == null) {
				continue;
			}
			String cellval = cell.getStringCellValue().toLowerCase().trim();
			if (!indexMap.containsKey(cellval)) {
				indexMap.put(cellval, i);
				if (excelHeadMinVal.contains(cellval)) {
					check++;
				}
			}
		}
		if (check < excelHeadMinVal.size()) {
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int rowCount = getRowsInSheet(sheet);
		result.setTotalDataCount(rowCount - 1);
		if (rowCount == 1) {
			result.setErrorMsg("没有任何数据");
			return result;
		}
		for (int i = 1; i < rowCount; i++) {
			XSSFRow row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			ArrayList<String> rowData = new ArrayList<>();
			for (int j = 0; j < colCount; j++) {
				XSSFCell cell = row.getCell(j);
				if (cell == null) {
					rowData.add("");
					continue;
				}
				try {
					rowData.add(cell.getStringCellValue());
				} catch (Exception e) {
					if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
						rowData.add((long) cell.getNumericCellValue() + "");
					} else {
						rowData.add("");
					}
				}
			}
			ImportExcelReprotModel<Score> report = getScoreByRow(rowData,
					indexMap);
			if (report.getErrorMsg() != null) {
				report.setErrorLine(i + 1);
			}
			result.getDataList().add(report);
		}
		return result;
	}

	/**
	 * 根据行数据和航索引生成包含学生及错误信息的bean
	 * 
	 * @param row
	 * @param map
	 * @return
	 */
	public ImportExcelReprotModel<Score> getScoreByRow(
			ArrayList<String> row, HashMap<String, Integer> map) {
		ImportExcelReprotModel<Score> report = new ImportExcelReprotModel<>();
		Score score = new Score();
		Project prj=new Project();
		score.setProject(prj);
		report.setData(score);
		String name = row.get(map.get("题目标题"));
		if (name == null || name.trim().isEmpty()) {
			report.setErrorMsg("未设置题目标题");
			return report;
		}
		report.getData().getProject().setName(name.trim());
		String zdScoreStr = row.get(map.get("指导教师成绩"));
		int zdScore=0;
		try {
			zdScore=Integer.parseInt(zdScoreStr);
		} catch (Exception e) {
			report.setErrorMsg("指导教师成绩格式错误");
		}
		if (zdScore<0||zdScore>40) {
			report.setErrorMsg("指导教师成绩分数错误");
			return report;
		}
		report.getData().setSy_score(zdScore);

		String pyScoreStr = row.get(map.get("评阅人成绩"));
		int pyScore=0;
		try {
			pyScore=Integer.parseInt(pyScoreStr);
		} catch (Exception e) {
			report.setErrorMsg("评阅人成绩格式错误");
		}
		if (pyScore<0||pyScore>20) {
			report.setErrorMsg("评阅人成绩分数错误");
			return report;
		}
		report.getData().setPy_score(pyScore);
		
		String dbScoreStr = row.get(map.get("答辩成绩"));
		int dbScore=0;
		try {
			dbScore=Integer.parseInt(dbScoreStr);
		} catch (Exception e) {
			report.setErrorMsg("答辩成绩格式错误");
		}
		if (dbScore<0||dbScore>40) {
			report.setErrorMsg("答辩成绩分数错误");
			return report;
		}
		report.getData().setDb_score(dbScore);
		return report;
	}

	/**
	 * 2003版获取实际excel的行数
	 * 
	 * @param sheet
	 * @return
	 */
	public int getRowsInSheet(HSSFSheet sheet) {
		int maxRow = sheet.getLastRowNum();
		int actureRowCount = 0;
		for (int i = 0; i < maxRow + 1; i++) {
			HSSFRow row = sheet.getRow(i);
			int maxCol = row.getLastCellNum();
			boolean colHasData = false;
			for (int j = 0; j < maxCol; j++) {
				HSSFCell cell = row.getCell(j);
				if (cell == null) {
					continue;
				}
				try {
					String val = cell.getStringCellValue();
					if (val == null || val.trim().isEmpty()) {
						continue;
					}
				} catch (Exception e) {
					String val = cell.getNumericCellValue()+"";
					if (val == null || val.trim().isEmpty()) {
						continue;
					}
				}
				colHasData = true;
				break;
			}
			if (colHasData) {
				actureRowCount = i + 1;
			}
		}
		return actureRowCount;
	}

	public int getRowsInSheet(XSSFSheet sheet) {
		int maxRow = sheet.getLastRowNum();
		int actureRowCount = 0;
		for (int i = 0; i < maxRow + 1; i++) {
			XSSFRow row = sheet.getRow(i);
			int maxCol = row.getLastCellNum();
			boolean colHasData = false;
			for (int j = 0; j < maxCol; j++) {
				XSSFCell cell = row.getCell(j);
				if (cell == null) {
					continue;
				}
				try {
					String val = cell.getStringCellValue();
					if (val == null || val.trim().isEmpty()) {
						continue;
					}
				} catch (Exception e) {
					String val = cell.getNumericCellValue()+"";
					if (val == null || val.trim().isEmpty()) {
						continue;
					}
				}
				colHasData = true;
				break;
			}
			if (colHasData) {
				actureRowCount = i + 1;
			}
		}
		return actureRowCount;
	}

	public static XSSFWorkbook exportTo2007(ArrayList<Score> scoreList) {
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet();
		XSSFRow headRow = sheet.createRow(0);
		headRow.createCell(0).setCellValue("题目标题");
		headRow.createCell(1).setCellValue("学生姓名");
		headRow.createCell(2).setCellValue("指导教师");
		headRow.createCell(3).setCellValue("指导教师成绩");
		headRow.createCell(4).setCellValue("评阅人成绩");
		headRow.createCell(5).setCellValue("答辩成绩");
		int scoreListSize = scoreList.size();
		for (int i = 0; i < scoreListSize; i++) {
			Score score = scoreList.get(i);
			XSSFRow row = sheet.createRow(i + 1);
			row.createCell(0).setCellValue(score.getProject().getName());
			row.createCell(1).setCellValue(score.getProject().getStudent().getName());
			row.createCell(2).setCellValue(score.getProject().getTeacher().getName());
			row.createCell(3).setCellValue(score.getSy_score());
			row.createCell(4).setCellValue(score.getPy_score());
			row.createCell(5).setCellValue(score.getDb_score());
		}
		return wb;
	}

	public static HSSFWorkbook exportTo2003(ArrayList<Score> scoreList) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFRow headRow = sheet.createRow(0);
		headRow.createCell(0).setCellValue("题目标题");
		headRow.createCell(1).setCellValue("学生姓名");
		headRow.createCell(2).setCellValue("指导教师");
		headRow.createCell(3).setCellValue("指导教师成绩");
		headRow.createCell(4).setCellValue("评阅人成绩");
		headRow.createCell(5).setCellValue("答辩成绩");
		int scoreListSize = scoreList.size();
		for (int i = 0; i < scoreListSize; i++) {
			Score score = scoreList.get(i);
			HSSFRow row = sheet.createRow(i + 1);
			row.createCell(0).setCellValue(score.getProject().getName());
			row.createCell(1).setCellValue(score.getProject().getStudent().getName());
			row.createCell(2).setCellValue(score.getProject().getTeacher().getName());
			row.createCell(3).setCellValue(score.getSy_score());
			row.createCell(4).setCellValue(score.getPy_score());
			row.createCell(5).setCellValue(score.getDb_score());
		}
		return wb;
	}
}
